package bingo.codeweaver.core.factory;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.sql.DataSource;

import net.bingosoft.common.BeanHelper;
import net.bingosoft.common.data.BaseDao;
import net.bingosoft.common.data.PersisterDao;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

import bingo.codeweaver.core.metadata.ColumnMetadata;
import bingo.codeweaver.core.metadata.DatabaseMetadata;
import bingo.codeweaver.core.metadata.TableMetadata;
import bingo.codeweaver.core.metadata.DatabaseMetadata.Vendor;
import bingo.codeweaver.core.utils.PropertiesHelper;

/**
 * Mar 6, 2009 6:51:28 AM DatabaseFactory.java
 * Author:yangdongyu(yangdy@bingosoft.net)
 * ReadMe:
 * ChangeLog:
 */
public class DatabaseFactory {

	private static Properties SQL_CONFIG = PropertiesHelper.loadProperties("sql.xml", DatabaseFactory.class);
	private static String oracle_getDatabaseTables = SQL_CONFIG.getProperty("oracle.getDatabaseTables");
	private static String oralce_getTableColumns = SQL_CONFIG.getProperty("oracle.getTableColumns");
	private static String oracle_buildRequiredTables = SQL_CONFIG.getProperty("oracle.buildRequiredTables");
	private static final String[] TYPES = {"TABLE", "VIEW"};
	private boolean lazyInit = true;

	public DatabaseFactory() {
		this.lazyInit = false;
	}
	public DatabaseFactory(boolean lazyInit) {
		this.lazyInit = lazyInit;
	}


	public boolean isLazyInit() {
		return lazyInit;
	}


	public void setLazyInit(boolean lazyInit) {
		this.lazyInit = lazyInit;
	}


	public DatabaseMetadata create(String username,String password,String tablespace,String url,String driverClassName) throws SQLException{

		DatabaseMetadata database = new DatabaseMetadata();
		database.setUsername(username);
		database.setPassword(password);
		database.setUrl(url);
		database.setDriver(driverClassName);
		database.setTablespace(tablespace);
		database.setFactory(this);
		BaseDao dao = new PersisterDao(new DriverManagerDataSource(driverClassName, url, username, password));

		DatabaseMetaData dbmd = dao.currentConnection().getMetaData();

		database.setProductName(dbmd.getDatabaseProductName());
		database.setProductVersion(dbmd.getDatabaseProductVersion());
		database.setDriverVersion(dbmd.getDriverVersion());

		String vendor = getVendorByDriverClassName(driverClassName);
		database.setVendor(Vendor.valueOf(vendor));
		database.setLazyInit(lazyInit);
		if(!lazyInit){//延迟加载数据库表
			if(Vendor.ORACLE.name().equals(vendor)){
				buildAllOracleDatabaseTables(username, database, dao);
			}else{
				buildDatabaseTables(database, dbmd);
			}
		}
		return database;
	}

	/**
	 * 生成数据库对象
	 * @param datasource
	 * @param tablespace
	 * @return
	 * @throws Exception

	 */
	public DatabaseMetadata create(DataSource datasource,String tablespace) throws Exception{

		DatabaseMetadata database = new DatabaseMetadata();
		BaseDao dao = new PersisterDao(datasource);
		DatabaseMetaData dbmd = dao.currentConnection().getMetaData();
		database.setUsername(dbmd.getUserName());
		database.setPassword(BeanHelper.getPropertyAsString(datasource, "password"));
		database.setUrl(dbmd.getURL());
		String vendor = getVendorByDriverClassName(database.getDriver());

		if(Vendor.ORACLE.name().equals(vendor)){
			database.setDriver("oracle.jdbc.driver.OracleDriver");
		}else{
			database.setDriver(dbmd.getDriverName());
		}

		if(tablespace == null){
			tablespace = database.getUsername().toUpperCase();
		}
		database.setTablespace(tablespace);

		database.setProductName(dbmd.getDatabaseProductName());
		database.setProductVersion(dbmd.getDatabaseProductVersion());
		database.setDriverVersion(dbmd.getDriverVersion());


		database.setVendor(Vendor.valueOf(vendor));
		database.setLazyInit(lazyInit);
		database.setFactory(this);
		if(!lazyInit){//延迟加载数据库表
			if(Vendor.ORACLE.name().equals(vendor)){
				buildAllOracleDatabaseTables(database.getUsername(), database, dao);
			}else{
				buildDatabaseTables(database, dbmd);
			}
		}
		return database;
	}


	protected void buildAllOracleDatabaseTables(String username,
			DatabaseMetadata database, BaseDao dao) {
		String tablespace = database.getTablespace();
		if(null == tablespace){
			tablespace = username.toUpperCase();
		}
		List<TableMetadata> tables = dao.list(TableMetadata.class, oracle_getDatabaseTables, tablespace);
		for(TableMetadata table : tables){
			table.setDatabase(database);
			String name = table.getName();
			List<Map<String,Object>> columns = dao.list(oralce_getTableColumns, name);
			for(Map <String,Object> map : columns){
				buildOracleTableColumns(table, map);
			}
		}
	}
	/**
	 * 构建Oracle的数据库表,只构建给定的数据库表
	 * @param database
	 * @param tableNames
	 */
	public void buildRequiredOracleDatabase(DatabaseMetadata database,String ...tableNames){
		BaseDao dao = new PersisterDao(new DriverManagerDataSource(database.getDriver(), database.getUrl(), database.getUsername(), database.getPassword()));
		String tablespace = database.getTablespace();
		if(null == tablespace){
			tablespace = database.getUsername().toUpperCase();
		}

		StringBuilder sb = new StringBuilder();
		for(String name : tableNames){
			sb.append(",'").append(name).append("'");

		}
		if(sb.length() > 0 && sb.charAt(0) == ','){
			sb.deleteCharAt(0);
		}
		String buildRequiredTables = oracle_buildRequiredTables.replaceFirst("%TableName%", sb.toString());
		List<TableMetadata> tables = dao.list(TableMetadata.class, buildRequiredTables, tablespace);
		for(TableMetadata table : tables){
			table.setDatabase(database);
			String name = table.getName();
			List<Map<String,Object>> columns = dao.list(oralce_getTableColumns, name);
			for(Map <String,Object> map : columns){
				buildOracleTableColumns(table, map);
			}
		}

	}


	protected void buildDatabaseTables(DatabaseMetadata database,
			DatabaseMetaData dbmd) throws SQLException {
		List<TableMetadata> list = new ArrayList<TableMetadata>();
		ResultSet rs = dbmd.getTables(null, null, null, TYPES);
		while(rs.next()){
			TableMetadata table = new TableMetadata();
			table.setSchema(rs.getString("TABLE_SCHEM"));
			table.setName(rs.getString("TABLE_NAME"));
			table.setComments(rs.getString("REMARKS"));
			table.setDatabase(database);
			list.add(table);

			buildDatabaseColumns(dbmd, table);
		}
	}
	
	
	protected void buildDatabaseColumns(DatabaseMetaData dbmd, TableMetadata table)
	throws SQLException {
		ResultSet rsColumn = dbmd.getColumns(null, null, table.getName(), null);

		while(rsColumn.next()){
			ColumnMetadata c = new ColumnMetadata();
			c.setName(rsColumn.getString("COLUMN_NAME"));
			c.setTypeName(rsColumn.getString("TYPE_NAME"));
			c.setNullable(rsColumn.getBoolean("NULLABLE"));
			c.setColumnSize(rsColumn.getInt("COLUMN_SIZE"));
			c.setComment(rsColumn.getString("REMARKS"));
			c.setDefaultValue(rsColumn.getString("COLUMN_DEF"));
			c.setDisplayOrder(rsColumn.getInt("ORDINAL_POSITION"));
			c.setDecimalDigits(rsColumn.getInt("DECIMAL_DIGITS"));
			c.setTable(table);

		}
		//设置主键信息
		ResultSet rsPrimaryKeys = dbmd.getPrimaryKeys(null, null, table.getName());
		while(rsPrimaryKeys.next()){
			table.addPrimaryKey(rsPrimaryKeys.getString("COLUMN_NAME"));
		}
		//读取和设置外键信息
		ResultSet rsForeignKeys = dbmd.getImportedKeys(null, null, null);
		while(rsForeignKeys.next()){
			String tableName = rsForeignKeys.getString("FKTABLE_NAME");
			String columnName = rsForeignKeys.getString("FKCOLUMN_NAME");
			if(tableName.equals(table.getName())){
				table.getColumnMetadata(columnName).setForeignKey(true);
			}
		}
	}

	protected static void buildOracleTableColumns(TableMetadata table, Map<String, Object> map) {
		if(table.getColumnMetadata(String.valueOf(map.get("COLUMN_NAME")))!=null){
			return;
		}
		ColumnMetadata column = new ColumnMetadata();
		column.setTable(table);
		column.setName(String.valueOf(map.get("COLUMN_NAME")));
		column.setTypeName(String.valueOf(map.get("DATA_TYPE")));
		//Number(10,2) DATA_PRECISION=10 DATA_SCALE=2
		if("NUMBER".equals(column.getTypeName())){
			column.setDecimalDigits(BeanHelper.getInt(map.get("DATA_PRECISION"),0));
			column.setScale(BeanHelper.getInt(map.get("DATA_SCALE"),0));
		}
		Object defaultValue = map.get("DATA_DEFAULT");
		if(defaultValue != null){
			String javaTypeName = column.getJavaTypeName();
			if("String".equals(javaTypeName)){
				defaultValue = "\"" +defaultValue.toString()+ "\"";
			}else if("Long".equals(javaTypeName)){
				defaultValue = defaultValue.toString()+ "L";
			}
			column.setDefaultValue(defaultValue.toString());
		}

		column.setComment(String.valueOf(map.get("COMMENTS")));

		column.setDisplayOrder(BeanHelper.getInt(map.get("COLUMN_ID")));
		column.setNullable("Y".equals(String.valueOf(map.get("NULLABLE"))));
		if("Y".equals(String.valueOf(map.get("IS_PK")))){
			table.addPrimaryKey(column.getName());
		}
		if("Y".equals(String.valueOf(map.get("IS_FK")))){
			column.setForeignKey(true);
		}

		if("String".equals(column.getJavaTypeName())){
			column.setLength(BeanHelper.getInt(map.get("DATA_LENGTH"),0));
		}
	}

	private static String getVendorByDriverClassName(String driverClassName) {
		return "ORACLE";
	}
}
